Dropping an Index of a MySQL InnoDB Table using Python

Overview:

An index makes database queries faster by avoiding of scanning the table from the beginning till the data is found.
A database index which is no longer required can be deleted through the SQL statement DROP INDEX.
In case a duplicate index has been created on to a table using Add Index option of the Alter Table SQL statement, it can be deleted through the SQL statement DROP INDEX. The SQL statement Create Index requires a name to be provided for the index being created. Hence creating a duplicate index is not possible with the SQL statement CREATE INDEX.

Example:

# Example Python program to delete a redundant index

# MySQL client import
import pymysql

# Create a connection to the MySQL Server
dbServerIP = "127.0.0.1" # MySQL DB server IP Address
dbUsrName = "root" # MySQL User name
dbUsrPwd = "BanniInside1$" # MySQL Password of the db user
charSet = "utf8mb4" # Character set
dbCursorType = pymysql.cursors.DictCursor # Cursor type to be used

dbSvrCon = pymysql.connect(host=dbServerIP, user=dbUsrName, password=dbUsrPwd,
charset=charSet, cursorclass=dbCursorType)

try:
# Cursor object
dbCur = dbSvrCon.cursor()

# SQL Statement - create table
createTableSQL = """create table workplace.Albums(Id INT NOT NULL AUTO_INCREMENT,
Album VARCHAR(128),
Artist VARCHAR(128),
Released DATE,
Genre VARCHAR(128),
Length TIME,
Producer VARCHAR(128),
PRIMARY KEY (Id)
)"""

# Execute the sql statement
dbCur.execute(createTableSQL)

# SQL Statement - create secondary index
sqlCreateIndex = """ALTER TABLE workplace.Albums ADD index (Album)"""

# Execute the sql - create secondary index
dbCur.execute(sqlCreateIndex)

# Redundant index is created
dbCur.execute(sqlCreateIndex)

# List indexes
sqlShowIndexes = "show index from workplace.Albums";

dbCur.execute(sqlShowIndexes)
indexList = dbCur.fetchall()

print(indexList)

########## Drop the redundant Index ##########
# Redundant indexes is created with a suffix of _2, _3 and so on.
sqlDropIndex = "ALTER TABLE workplace.Albums DROP index Album_2"
dbCur.execute(sqlDropIndex)

# Print the index list
dbCur.execute(sqlShowIndexes)
indexList = dbCur.fetchall()
print(indexList)

except Exception as e:
print("Exception occured:{}".format(e))

finally:
dbSvrCon.close()

Output:

[{'Table': 'Albums', 'Non_unique': 0, 'Key_name': 'PRIMARY', 'Seq_in_index': 1, 'Column_name': 'Id', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': '', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': '', 'Visible': 'YES', 'Expression': None}, {'Table': 'Albums', 'Non_unique': 1, 'Key_name': 'Album', 'Seq_in_index': 1, 'Column_name': 'Album', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': 'YES', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': '', 'Visible': 'YES', 'Expression': None}, {'Table': 'Albums', 'Non_unique': 1, 'Key_name': 'Album_2', 'Seq_in_index': 1, 'Column_name': 'Album', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': 'YES', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': '', 'Visible': 'YES', 'Expression': None}]

...